#!/usr/bin/env python
# -*- coding:utf-8 -*-

import inspect
import os
import sqlite3
import threading
import time
from lib.common_lib import localtime

lock = threading.Lock()


# 对sqlite操作枷锁
def execute_sql(cursor, sql):
    try:
        lock.acquire(True)
        cursor.execute(sql)
        res = cursor.fetchall()
        return res
    except Exception as err:
        print err
    finally:
        lock.release()


class NSDb:
    def __init__(self):
        BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
        self.db_name = BASE_DIR + "/db.sqlite3"
        self.conn = sqlite3.connect(self.db_name, check_same_thread=False)
        self.cur = self.conn.cursor()

    def init_ns_base_tb(self):
        init_sql = ('\n'
                    '        CREATE TABLE IF NOT EXISTS ns_base (\n'
                    '          ID INTEGER PRIMARY KEY AUTOINCREMENT,\n'
                    '          HOSTNAME TEXT NOT NULL,\n'
                    '          IP TEXT NOT NULL,\n'
                    '          CAPTURETIME INTEGER NOT NULL,\n'
                    '          CPU TEXT NOT NULL,\n'
                    '          MEM TEXT NOT NULL,\n'
                    '          SWAP TEXT NOT NULL,\n'
                    '          OSNAME TEXT NOT NULL,\n'
                    '          KERNEL TEXT NOT NULL,\n'
                    '          UPTIME TEXT NOT NULL\n'
                    '        )\n'
                    '        ')
        try:
            self.cur.execute(init_sql)
        except Exception as err:
            print err
        self.conn.commit()

    def init_ns_cpu_tb(self):
        init_sql = ('\n'
                    '        CREATE TABLE IF NOT EXISTS ns_cpu (\n'
                    '          ID INTEGER PRIMARY KEY AUTOINCREMENT,\n'
                    '          HOSTNAME TEXT NOT NULL,\n'
                    '          IP TEXT NOT NULL,\n'
                    '          CAPTURETIME INTEGER NOT NULL,\n'
                    '          USER_CPU INTEGER NOT NULL,\n'
                    '          NICE_CPU INTEGER NOT NULL,\n'
                    '          SYSTEM_CPU INTEGER NOT NULL,\n'
                    '          IOWAIT_CPU INTEGER NOT NULL,\n'
                    '          IDLE_CPU INTEGER NOT NULL,\n'
                    '          USAGE_CPU INTEGER NOT NULL,\n'
                    '          LOAD_AVG INTEGER NOT NULL\n'
                    '        )\n'
                    '        ')
        self.cur.execute(init_sql)
        self.conn.commit()

    def init_ns_mem_tb(self):
        init_sql = ('\n'
                    '        CREATE TABLE IF NOT EXISTS ns_mem (\n'
                    '          ID INTEGER PRIMARY KEY AUTOINCREMENT,\n'
                    '          HOSTNAME TEXT NOT NULL,\n'
                    '          IP TEXT NOT NULL,\n'
                    '          CAPTURETIME INTEGER NOT NULL,\n'
                    '          MEM_TOTAL INTEGER NOT NULL,\n'
                    '          MEM_USAGE INTEGER NOT NULL,\n'
                    '          MEM_FREE INTEGER NOT NULL,\n'
                    '          SWAP_TOTAL INTEGER NOT NULL,\n'
                    '          SWAP_USED INTEGER NOT NULL,\n'
                    '          SWAP_FREE INTEGER NOT NULL,\n'
                    '          MEM_PERCENT INTEGER NOT NULL\n'
                    '        )\n'
                    '        ')
        self.cur.execute(init_sql)
        self.conn.commit()

    def init_ns_disk_tb(self):
        init_sql = ('\n'
                    '        CREATE TABLE IF NOT EXISTS ns_disk (\n'
                    '          ID INTEGER PRIMARY KEY AUTOINCREMENT,\n'
                    '          HOSTNAME TEXT NOT NULL,\n'
                    '          IP TEXT NOT NULL,\n'
                    '          CAPTURETIME INTEGER NOT NULL,\n'
                    '          DISK TEXT NOT NULL,\n'
                    '          DISK_IO TEXT NOT NULL\n'
                    '        )\n'
                    '        ')
        self.cur.execute(init_sql)
        self.conn.commit()

    def init_ns_net_tb(self):
        init_sql = ('\n'
                    '        CREATE TABLE IF NOT EXISTS ns_net (\n'
                    '          ID INTEGER PRIMARY KEY AUTOINCREMENT,\n'
                    '          HOSTNAME TEXT NOT NULL,\n'
                    '          IP TEXT NOT NULL,\n'
                    '          CAPTURETIME INTEGER NOT NULL,\n'
                    '          INTERFACE TEXT NOT NULL,\n'
                    '          TRAFFIC_IN INTEGER NOT NULL,\n'
                    '          TRAFFIC_OUT INTEGER NOT NULL,\n'
                    '          SOCKETS TEXT NOT NULL\n'
                    '        )\n'
                    '        ')
        self.cur.execute(init_sql)
        self.conn.commit()

    def get_server_list(self):
        query_sql = """
            SELECT id, hostname, ip, cpu, mem, swap, osname, kernel, uptime, capturetime
            FROM ns_base
            GROUP BY ip
            ORDER BY capturetime DESC
        """
        res = execute_sql(self.cur, query_sql)
        data_maps = []
        for item in res:
            temp = {
                "id": item[0],
                "hostname": item[1],
                "ip": item[2],
                "cpu": item[3],
                "mem": item[4],
            }
            data_maps.append(temp)

        return data_maps

    def get_info_list_by_ip(self, ip, start, num):
        query_sql = """
                    SELECT id, hostname, ip, cpu, mem, swap, osname, kernel, uptime, capturetime
                    FROM ns_base
                    WHERE ip='{IP}'
                    ORDER BY capturetime DESC
                    LIMIT {NUM} OFFSET {START}
                """.format(IP=ip, NUM=num, START=start)

        res = execute_sql(self.cur, query_sql)
        data_maps = []
        for item in res:
            temp = {
                "id": item[0],
                "hostname": item[1],
                "ip": item[2],
                "cpu": item[3],
                "mem": item[4],
                "swap": item[5],
                "osname": item[6],
                "kernel": item[7],
                "uptime": item[8],
                "capturetime": item[9],
            }
            data_maps.append(temp)

        return data_maps

    def get_disk_info_by_ip(self, ip):
        query_sql = """
                    SELECT id, hostname, ip, capturetime, disk, disk_io
                    FROM ns_disk
                    WHERE ip='{IP}'
                    ORDER BY capturetime DESC
                    LIMIT {NUM} OFFSET {START}
                """.format(IP=ip, NUM=1, START=0)
        res = execute_sql(self.cur, query_sql)
        data_maps = []
        for item in res:
            temp = {
                "id": item[0],
                "hostname": item[1],
                "ip": item[2],
                "capturetime": item[3],
                "disk": item[4],
                "disk_io": item[5],
            }
            data_maps.append(temp)

        return data_maps

    def get_disk_list_by_ip(self, ip, start, num):
        query_sql = """
                    SELECT disk, disk_io, capturetime
                    FROM ns_disk
                    WHERE ip='{IP}'
                    ORDER BY capturetime DESC
                    LIMIT {NUM} OFFSET {START}
                """.format(IP=ip, NUM=num, START=start)
        res = execute_sql(self.cur, query_sql)
        res.reverse()
        return zip(*res)

    def get_cpu_list_by_ip(self, ip, start, num):
        query_sql = """
                            SELECT usage_cpu, user_cpu, nice_cpu, system_cpu, iowait_cpu, idle_cpu, load_avg, capturetime
                            FROM ns_cpu
                            WHERE ip='{IP}'
                            ORDER BY capturetime DESC
                            LIMIT {NUM} OFFSET {START}
                        """.format(IP=ip, NUM=num, START=start)
        res = execute_sql(self.cur, query_sql)
        if len(res) <= 0:
            return []

        res.reverse()
        return zip(*res)

    def get_load_list_by_ip(self, ip, start, num):
        query_sql = """SELECT load_avg, capturetime
                        FROM ns_cpu
                        WHERE ip='{IP}'
                        ORDER BY capturetime DESC
                        LIMIT {NUM} OFFSET {START}
        """.format(IP=ip, NUM=num, START=start)
        res = execute_sql(self.cur, query_sql)
        if len(res) <= 0:
            return []

        res.reverse()
        return zip(*res)

    def get_mem_list_by_ip(self, ip, start, num):
        query_sql = """SELECT mem_usage, mem_free, swap_used, swap_free, capturetime
                                FROM ns_mem
                                WHERE ip='{IP}'
                                ORDER BY capturetime DESC
                                LIMIT {NUM} OFFSET {START}
                """.format(IP=ip, NUM=num, START=start)
        res = execute_sql(self.cur, query_sql)
        if len(res) <= 0:
            return []

        res.reverse()
        return zip(*res)

    def add_base_info(self, hostname, ip, cpu, mem, swap, osname, kernel, uptime):
        insert_sql = """
            INSERT INTO ns_base(hostname, ip, cpu, mem, swap, osname, kernel, uptime, capturetime)
            VALUES('{hostname}', '{ip}', '{cpu}', '{mem}', '{swap}', '{osname}', '{kernel}', '{uptime}', {capturetime});
        """.format(hostname=hostname, ip=ip, cpu=cpu, mem=mem, swap=swap,
                   osname=osname, kernel=kernel, uptime=uptime, capturetime=localtime())

        self.cur.execute(insert_sql)
        self.conn.commit()

    def add_cpu_info(self, hostname, ip, user_cpu, nice_cpu, system_cpu, iowait_cpu, idle_cpu, usage_cpu, load_avg):
        insert_sql = """
            INSERT INTO ns_cpu(hostname, ip, user_cpu, nice_cpu, system_cpu, iowait_cpu, idle_cpu, usage_cpu, load_avg, capturetime)
            VALUES('{hostname}', '{ip}', '{user_cpu}', '{nice_cpu}', '{system_cpu}', '{iowait_cpu}', '{idle_cpu}', '{usage_cpu}', '{load_avg}', '{capturetime}');
        """.format(hostname=hostname, ip=ip, user_cpu=user_cpu, nice_cpu=nice_cpu,
                   system_cpu=system_cpu, iowait_cpu=iowait_cpu, idle_cpu=idle_cpu, usage_cpu=usage_cpu, load_avg=load_avg, capturetime=localtime())

        self.cur.execute(insert_sql)
        self.conn.commit()

    def add_disk_info(self, hostname, ip, disk, disk_io):
        insert_sql = """
            INSERT INTO ns_base(hostname, ip, disk, disk_io, capturetime)
            VALUES('{hostname}', '{ip}', '{disk}', '{disk_io}', {capturetime});
        """.format(hostname=hostname, ip=ip, disk=disk, disk_io=disk_io, capturetime=localtime())

        self.cur.execute(insert_sql)
        self.conn.commit()

    def add_mem_info(self, hostname, ip, mem_total, mem_usage, mem_free, swap_total, swap_used, swap_free, mem_percent):
        insert_sql = """
            INSERT INTO ns_mem(hostname, ip, mem_total, mem_usage, mem_free, swap_total, swap_used, swap_free, mem_percent, capturetime)
            VALUES('{hostname}', '{ip}', '{mem_total}', '{mem_usage}', '{mem_free}', '{swap_total}', 
            '{swap_used}', '{swap_free}', '{mem_percent}', '{capturetime}');
        """.format(hostname=hostname, ip=ip, mem_total=mem_total, mem_usage=mem_usage, mem_free=mem_free,
                   swap_total=swap_free, swap_used=swap_used, swap_free=swap_free, mem_percent=mem_percent, capturetime=localtime())

        self.cur.execute(insert_sql)
        self.conn.commit()

    def run_all_init_func(self):
        """
        自动获取NSDb类里的所有init_ns_xxx方法，依次执行建表
        """
        for func in inspect.getmembers(self, predicate=inspect.ismethod):
            if func[0][:7] == 'init_ns':
                func[1]()
